<!DOCTYPE html>
<html lang="zh-CN">

<!-- Head tag -->
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  
  <meta name="baidu-site-verification" content="undefined" />
  <meta name="google-site-verification" content="undefined" />
  <meta name="msvalidate.01" content="undefined" />
  <meta name="360-site-verification" content="undefined" />
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <!-- if page has tags, then add tags to keyword -->
  
  
  <meta name="keywords" content=",MySQL,MySQL优化,">
  <!-- page.description has higher priority -->
  <meta name="description" content="MySQL优化神器Explain">
  <link rel="shortcut icon" href="/blog/img/logo.webp">
  <title>
    
    EXPLAIN-执行计划查询命令 | Evrn
    
  </title>

  <link rel="canonical" href="/blog/post/mysql-optimize-explain.html">
  
<link rel="stylesheet" href="/blog/css/reset.css">

  
<link rel="stylesheet" href="/blog/css/layout.css">

  
<link rel="stylesheet" href="/blog/css/markdown.css">

  <!-- Pygments Highlight CSS -->
  
<link rel="stylesheet" href="/blog/css/highlight.css">

  
<link rel="stylesheet" href="/blog/css/geektutu.css">

  <!-- global function -->
  <script>
    window.globalAddScript = function (url, onload, onerror) {
      var s = document.createElement('script');
      s.src = url;
      onload && (s.onload = onload);
      onerror && (s.onerror = onerror);
      document.body.appendChild(s);
    }
    window.globalAddCss = function (url) {
      var s = document.createElement('link');
      s.rel = 'stylesheet';
      s.href = url;
      document.body.appendChild(s);
    }
    window.getPosition = function (ele) {
      var x = 0, y = 0;
      while (ele) {
        x += (ele.offsetLeft - ele.scrollLeft + ele.clientLeft);
        y += (ele.offsetTop - ele.scrollTop + ele.clientTop);
        ele = ele.offsetParent;
      }
      return { x: x, y: y };
    }
    window.getDom = function (str) { return document.querySelector(str) }
  </script>
  <!-- google ad -->
  
<meta name="generator" content="Hexo 4.2.0"></head>

<body>
    <header class="gkt-header col-xs-12 padding-0">
    <div id="gkt-nav" class="gkt-header-container">
        <a href="/blog/" class="gkt-header-title float-left">
            <img class="float-left" src="/blog/img/logo.webp" alt="">
            <span>Evrn</span>
        </a>
        <nav class="gkt-header-nav text-right">
            <ul>
                <li><a class="hidden-xs" href="/blog/">主页</a></li>
                <li><a href="/blog/series/">专题</a></li>
                <li><a href="/blog/archives/">归档</a></li>
                <li><a href="/blog/post/link.html">友链</a></li>
                <li><a href="/blog/post/about.html">留言</a></li>
            </ul>
        </nav>
    </div>
    <div id="gkt-cate-nav" class="gkt-header-container hidden-xs">
        
        <nav class="gkt-header-nav float-left">
            <ul>
                
                
                <li class="gkt-cate-name float-left ">
                    <a class="float-left" href="/blog/post/go-learn-interface.html">Golang学习</a>
                    
                </li>
                
                <li class="gkt-cate-name float-left ">
                    <a class="float-left" href="/blog/post/go-asm-indroduce.html">Golang汇编</a>
                    
                </li>
                
                <li class="gkt-cate-name float-left ">
                    <a class="float-left" href="/blog/post/tcp-three-way-handshake.html">基础</a>
                    
                </li>
                
                <li class="gkt-cate-name float-left ">
                    <a class="float-left" href="/blog/post/go-how-to-build.html">Golang底层</a>
                    
                </li>
                
                <li class="gkt-cate-name float-left active">
                    <a class="float-left" href="/blog/post/mysql-index-use.html">MySQL</a>
                    
                </li>
                
            </ul>
        </nav>
    </div>
</header>
<div style="height: 44px"></div>
<div class="hidden-xs" style="height: 44px"></div>
<script>
    (function () {
        window.addEventListener('scroll', function () {
            if (window.innerWidth < 768) {
                return;
            }
            var nav = document.querySelector('#gkt-nav');
            var scrollTop = document.documentElement.scrollTop || document.body.scrollTop;
            scrollTop > 50 && (nav.classList.add('hide'));
            scrollTop <= 50 && (nav.classList.remove('hide'));
        });
        var cateNavs = document.querySelectorAll('#gkt-cate-nav>nav>ul>li');
        [].slice.call(cateNavs).forEach(function (item) {
            var sub = item.querySelector('.gkt-sub-cate');
            if (!sub) return;
            item.addEventListener('mouseenter', function (e) { sub.style.display = 'block'; }, false);
            item.addEventListener('mouseleave', function (e) { sub.style.display = 'none'; }, false);
        })
    })();
</script>
    <!-- Main Content -->
    <div class="main-container">
        <!-- Main Content -->
<main class="col-xs-12 padding-0 markdown-it">
    <!-- Post Container -->
    
    
    <!-- Post Content -->
<div class="float-left post-container box-shadow">
    <div class="u-arrow-wrapper hidden-xs">
        
        <a class="float-left" href="/blog/post/mysql-index-base.html"><i class="u-arrow-left"></i></a>
        
        
        <a class="float-right" href="/blog/post/go-learn-lib-sorts.html"><i class="u-arrow-right"></i></a>
        
    </div>
    <article class="col-xs-12">
        <h1> EXPLAIN-执行计划查询命令 </h1>

        

        
        <div class="hidden-lg hidden-md series_links">
            <p> <strong> MySQL系列文章链接：</strong></p>
            <ul>
                
                <li>
                    <a href="/blog/post/mysql-index-use.html">《高性能MySQL》笔记-索引优化</a>
                    <span class="post-item-date">(Mar 1, 2020)</span>
                </li>
                
                <li>
                    <a href="/blog/post/mysql-index-base.html">《高性能MySQL》笔记-索引基础</a>
                    <span class="post-item-date">(Mar 2, 2020)</span>
                </li>
                
                <li>
                    <a href="/blog/post/mysql-optimize-explain.html">EXPLAIN-执行计划查询命令</a>
                    <span class="post-item-date">(Mar 3, 2020)</span>
                </li>
                
            </ul>
        </div>
        
        
        
        <h2 id="Explain-输出的列"><a href="#Explain-输出的列" class="headerlink" title="Explain 输出的列"></a>Explain 输出的列</h2><table>
<thead>
<tr>
<th>列名</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td>id</td>
<td>表/子查询的id</td>
</tr>
<tr>
<td>select_type</td>
<td>表/子查询的类型</td>
</tr>
<tr>
<td>table</td>
<td>表名</td>
</tr>
<tr>
<td>partitions</td>
<td>分区</td>
</tr>
<tr>
<td>type</td>
<td>数据查找类型</td>
</tr>
<tr>
<td>possible_keys</td>
<td>可供选择使用的索引</td>
</tr>
<tr>
<td>key</td>
<td>实际使用的索引</td>
</tr>
<tr>
<td>key_len</td>
<td>实际使用索引的长度</td>
</tr>
<tr>
<td>ref</td>
<td>与索引比较的列</td>
</tr>
<tr>
<td>rows</td>
<td>预估检测行数</td>
</tr>
<tr>
<td>filtered</td>
<td>依据表条件过滤行占比</td>
</tr>
<tr>
<td>Extra</td>
<td>其他信息</td>
</tr>
</tbody></table>
<h2 id="介绍"><a href="#介绍" class="headerlink" title="介绍"></a>介绍</h2><p>explain 命令可以用来分析select 语句的运行效果，例如使用的索引情况、排序的情况等等。</p>
<p>每个表输出一行</p>
<p>表可以指的是子查询，一个UNION结果</p>
<p>如果是两个表的联接，就有两行</p>
<p>自己联接自己也有两行，别名表单也算一行</p>
<h2 id="局限"><a href="#局限" class="headerlink" title="局限"></a>局限</h2><ul>
<li>无法显示触发器存储过程或UDF对查询的影响</li>
<li>不支持存储过程</li>
<li>不是百分百准确不会显示某些特定的优化</li>
<li>不区分相同名字的食物例如对内存排序和临时文件都是用“filesort”，对磁盘和内存中的临时表都显示“Using temporary”</li>
</ul>
<h2 id="调用"><a href="#调用" class="headerlink" title="调用"></a>调用</h2><p>在查询中的SELECT关键字前增加EXPLAIN</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">SELECT</span> select_options</span><br></pre></td></tr></table></figure>

<p>执行后还通过SHOW WARNINGS查看查询计划生成的语句，可以借此查看优化器是如何转化语句的。</p>
<h2 id="输出列介绍"><a href="#输出列介绍" class="headerlink" title="输出列介绍"></a>输出列介绍</h2><h3 id="id列"><a href="#id列" class="headerlink" title="id列"></a>id列</h3><p>表示查询中涉及到的表被搜索的顺序，相同Id的执行顺序<strong>从上往下</strong>，不同的Id的执行顺序<strong>从大到小</strong>内层的查询先被执行</p>
<h3 id="select-type列"><a href="#select-type列" class="headerlink" title="select_type列"></a>select_type列</h3><p>显示对应行是简单还是复杂SELECT(简单子查询，派生表（FROM子句中的子查询），UNION查询)</p>
<h4 id="SIMPLE"><a href="#SIMPLE" class="headerlink" title="SIMPLE"></a>SIMPLE</h4><p>意味着查询不包括子查询和UNION</p>
<h4 id="PRIMARY"><a href="#PRIMARY" class="headerlink" title="PRIMARY"></a>PRIMARY</h4><p>如果有任何子部分最外层的部分标记为<strong>PRIMARY</strong>，<strong>其他部分</strong>标记为</p>
<h4 id="SUBQUERY"><a href="#SUBQUERY" class="headerlink" title="SUBQUERY"></a>SUBQUERY</h4><p>包含在<strong>SELECT列表</strong>中或者<strong>WHERE条件</strong>中的子查询（<strong>不在FROM子</strong>句中）标记为SUBQUERY</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> t1 <span class="keyword">where</span> <span class="keyword">id</span>=(<span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">from</span> t2 <span class="keyword">limit</span> <span class="number">1</span>);</span><br><span class="line"><span class="keyword">select</span> (<span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">from</span> t2 <span class="keyword">limit</span> <span class="number">1</span>) <span class="keyword">from</span> t1 <span class="keyword">limit</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h4 id="DEPENDENT-SUBQUERY"><a href="#DEPENDENT-SUBQUERY" class="headerlink" title="DEPENDENT SUBQUERY"></a>DEPENDENT SUBQUERY</h4><p>查询中的条件依赖于子查询的结果</p>
<h4 id="UNCACHEABLE-SUBQUERY"><a href="#UNCACHEABLE-SUBQUERY" class="headerlink" title="UNCACHEABLE SUBQUERY"></a>UNCACHEABLE SUBQUERY</h4><p> 不能缓存结果的子查询，并且必须为外部查询的每一行重新计算结果，例如子查询中有用户变量@xxx</p>
<h4 id="DERIVED"><a href="#DERIVED" class="headerlink" title="DERIVED"></a>DERIVED</h4><p>表示包含在FROM子句中的子查询SELECT，会生成一个内部称为派生表的临时表，供外层查询</p>
<p>当在FROM子句有子查询时，table列显示&lt;derivedN&gt;的形式，N表示子查询id列的id，N总是指向在他后面的行。</p>
<h4 id="UNION"><a href="#UNION" class="headerlink" title="UNION"></a>UNION</h4><p>联合查询，用来标识UNION中<strong>第二个</strong>和随后的SELECT，第一个SELECT被标记为PRIMARY。</p>
<p>如果UNION包含在FROM子句中的子查询，那么它的第一个SELECT会被标记为DERIVED</p>
<blockquote>
<p>因为整个FROM子查询都会被作为整体产生一个派生表</p>
</blockquote>
<h4 id="UNION-RESULT"><a href="#UNION-RESULT" class="headerlink" title="UNION RESULT"></a>UNION RESULT</h4><p>用来标记从UNION中生成的匿名临时表检索结果的select。</p>
<p>当有UNION时，UNION RESULT的table列包含一个参与UNION的id列表。总是指向前面的行的id。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">SELECT</span></span><br><span class="line">	a.title </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	( </span><br><span class="line">		<span class="keyword">SELECT</span> film.title </span><br><span class="line">		<span class="keyword">FROM</span> film </span><br><span class="line">		<span class="keyword">UNION</span> </span><br><span class="line">		<span class="keyword">SELECT</span> actor.first_name <span class="keyword">FROM</span> actor </span><br><span class="line">) a;</span><br></pre></td></tr></table></figure>

<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>PRIMARY</td>
<td>&lt;derived2&gt;</td>
</tr>
<tr>
<td>2</td>
<td>DERIVED</td>
<td>film</td>
</tr>
<tr>
<td>3</td>
<td>UNION</td>
<td>actor</td>
</tr>
<tr>
<td>NULL</td>
<td>UNION RESULT</td>
<td>&lt;union2,3&gt;</td>
</tr>
</tbody></table>
<p>id:2,3属于FROM子查询，第一个select被标记为DERIVED，整个派生出 derived2 临时表</p>
<p>最后一行是查询联合id:2,3的结果，因为在select语句中没有这个临时表，因此为NULL</p>
<h3 id="table列"><a href="#table列" class="headerlink" title="table列"></a>table列</h3><p>显示对应行访问哪个表，如果有别名就显示别名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">SELECT</span></span><br><span class="line">	film.film_id </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	sakila.film</span><br><span class="line">	<span class="keyword">INNER</span> <span class="keyword">JOIN</span> sakila.film_actor <span class="keyword">USING</span> ( film_id )</span><br><span class="line">	<span class="keyword">INNER</span> <span class="keyword">JOIN</span> sakila.actor <span class="keyword">USING</span> ( actor_id )</span><br></pre></td></tr></table></figure>
<p>输出<br>|   id   |   select_type   |    table  |<br>| —- | —- | —- |<br>|   1   |  SIMPLE    |  actor    |<br>|   1   |  SIMPLE    |   film_actor   |<br>|   1   |   SIMPLE   |   film   |</p>
<p>可以看到关联顺序<code>actor-&gt;film_actor-&gt;film</code>符合MySQL左侧深度优先树法则</p>
<p><img src="http://river.marvelthanos.com/blog/20200323/mkU9xy4K9Kpm.png?imageslim" alt="mark"></p>
<h4 id="复杂sql举例"><a href="#复杂sql举例" class="headerlink" title="复杂sql举例"></a>复杂sql举例</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">SELECT</span></span><br><span class="line">	actor_id,</span><br><span class="line">	( <span class="keyword">SELECT</span> <span class="number">1</span> <span class="keyword">FROM</span> sakila.film_actor <span class="keyword">WHERE</span> film_actor.actor_id = der_1.actor_id <span class="keyword">LIMIT</span> <span class="number">1</span> ) </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	( <span class="keyword">SELECT</span> actor_id <span class="keyword">FROM</span> sakila.actor <span class="keyword">LIMIT</span> <span class="number">5</span> ) <span class="keyword">AS</span> der_1 </span><br><span class="line"><span class="keyword">UNION</span> <span class="keyword">ALL</span></span><br><span class="line">	<span class="keyword">SELECT</span></span><br><span class="line">		film_id,</span><br><span class="line">		( <span class="keyword">SELECT</span> @var1 <span class="keyword">FROM</span> sakila.rental <span class="keyword">LIMIT</span> <span class="number">1</span> ) </span><br><span class="line">	<span class="keyword">FROM</span></span><br><span class="line">		( <span class="keyword">SELECT</span> film_id, </span><br><span class="line">         (<span class="keyword">SELECT</span> <span class="number">1</span> <span class="keyword">FROM</span> sakila.store <span class="keyword">LIMIT</span> <span class="number">1</span> ) </span><br><span class="line">         <span class="keyword">FROM</span> sakila.film <span class="keyword">LIMIT</span> <span class="number">5</span></span><br><span class="line">        ) <span class="keyword">AS</span> der_2;</span><br></pre></td></tr></table></figure>

<table>
<thead>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>PRIMARY</td>
<td>&lt;derived3&gt;</td>
</tr>
<tr>
<td>3</td>
<td>DERIVED</td>
<td>actor</td>
</tr>
<tr>
<td>2</td>
<td>DEPENDENT SUBQUERY</td>
<td>film_actor</td>
</tr>
<tr>
<td>4</td>
<td>UNION</td>
<td>&lt;derived6&gt;</td>
</tr>
<tr>
<td>6</td>
<td>DERIVED</td>
<td>film</td>
</tr>
<tr>
<td>7</td>
<td>SUBQUERY</td>
<td>store</td>
</tr>
<tr>
<td>5</td>
<td>UNCACHEABLE SUBQUERY</td>
<td>rental</td>
</tr>
</tbody></table>
<p>按id从大到小开始执行，最大的是7，</p>
<p>id: 7 在select列表中因此是SUBQUERY</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="number">1</span> <span class="keyword">FROM</span> sakila.store <span class="keyword">LIMIT</span> <span class="number">1</span></span><br></pre></td></tr></table></figure>

<p>id: 6 在from子查询因此是DERIVED</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> film_id, </span><br><span class="line">(<span class="keyword">SELECT</span> <span class="number">1</span> <span class="keyword">FROM</span> sakila.store <span class="keyword">LIMIT</span> <span class="number">1</span> ) </span><br><span class="line"><span class="keyword">FROM</span> sakila.film <span class="keyword">LIMIT</span> <span class="number">5</span></span><br></pre></td></tr></table></figure>

<p>id: 5 在select列表中的子查询因此是SUBQUERY</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> @var1 <span class="keyword">FROM</span> sakila.rental <span class="keyword">LIMIT</span> <span class="number">1</span></span><br></pre></td></tr></table></figure>

<p>可以看到如果type输出DERIVED表示一个“嵌套查询”的开始，如果后续行的id更小（这里的5小于6）意味着嵌套查询已经被关闭，我们知道id: 7是派生表derived6的一部分。</p>
<p>id: 2 在 更高的id （3大于2）后面，说明他是后执行,依赖于der_1的结果，因此是<code>DEPENDENT SUBQUERY</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="number">1</span> <span class="keyword">FROM</span> sakila.film_actor <span class="keyword">WHERE</span> film_actor.actor_id = der_1.actor_id <span class="keyword">LIMIT</span> <span class="number">1</span></span><br></pre></td></tr></table></figure>

<p>id: 3</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">( <span class="keyword">SELECT</span> actor_id <span class="keyword">FROM</span> sakila.actor <span class="keyword">LIMIT</span> <span class="number">5</span> ) <span class="keyword">AS</span> der_1</span><br></pre></td></tr></table></figure>

<h3 id="type列"><a href="#type列" class="headerlink" title="type列"></a>type列</h3><p>表示查询的访问类型，是我们检查当前语句是否需要优化，能够继续优化的重要指标</p>
<p>结果值<strong>最差到最优</strong>     ALL -&gt;  index -&gt;  range -&gt;  ref -&gt;  eq_ref -&gt; const-&gt;system -&gt; NULL</p>
<h4 id="ALL"><a href="#ALL" class="headerlink" title="ALL"></a>ALL</h4><p>全表扫描</p>
<h4 id="index"><a href="#index" class="headerlink" title="index"></a>index</h4><p>按索引的次序进行全表扫描，避免了排序，但是需要按随机次序按随机次序访问行（回表）</p>
<p>如果同时Extra列显示“Using index”，说明正在使用索引覆盖，不用回表，比全表扫描开销要少。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--索引覆盖Using index</span></span><br><span class="line"><span class="keyword">explain</span> <span class="keyword">select</span> film_id  <span class="keyword">from</span> film_actor <span class="keyword">ORDER</span> <span class="keyword">BY</span> film_id</span><br></pre></td></tr></table></figure>

<h4 id="range"><a href="#range" class="headerlink" title="range"></a>range</h4><p>有限制的索引扫描，只扫描一部分。</p>
<p>此时key列会标识使用的是哪个索引，key_len列包含使用的最长的索引长度，ref列为NULL</p>
<p>一般是指包含索引列的  <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal" target="_blank" rel="noopener"><code>=</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_not-equal" target="_blank" rel="noopener"><code>&lt;&gt;</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_greater-than" target="_blank" rel="noopener"><code>&gt;</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_greater-than-or-equal" target="_blank" rel="noopener"><code>&gt;=</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_less-than" target="_blank" rel="noopener"><code>&lt;</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_less-than-or-equal" target="_blank" rel="noopener"><code>&lt;=</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_is-null" target="_blank" rel="noopener"><code>IS NULL</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to" target="_blank" rel="noopener"><code>&lt;=&gt;</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_between" target="_blank" rel="noopener"><code>BETWEEN</code></a>, <a href="https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like" target="_blank" rel="noopener"><code>LIKE</code></a>, or <a href="https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_in" target="_blank" rel="noopener"><code>IN()</code></a> </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> film   <span class="keyword">where</span> <span class="keyword">length</span>  <span class="keyword">BETWEEN</span> <span class="number">40</span> <span class="keyword">and</span> <span class="number">50</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> film   <span class="keyword">where</span> <span class="keyword">length</span>  <span class="keyword">in</span> (<span class="number">40</span>,<span class="number">41</span>,<span class="number">42</span>,<span class="number">43</span>,<span class="number">44</span>,<span class="number">50</span>)</span><br></pre></td></tr></table></figure>

<blockquote>
<p>当between或者in or里面内容太多时，涉及的数据量太大，优化器会认为还是用ALL全表查询比较方便</p>
</blockquote>
<h4 id="index-subquery"><a href="#index-subquery" class="headerlink" title="index_subquery"></a>index_subquery</h4><p>这个join类型和<code>unique_subquery</code>有些类似。它在子查询中替换，但在以下形式的子查询中适用于非唯一索引:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">value IN (<span class="keyword">SELECT</span> key_column <span class="keyword">FROM</span> single_table <span class="keyword">WHERE</span> some_expr)</span><br></pre></td></tr></table></figure>

<h4 id="unique-subquery"><a href="#unique-subquery" class="headerlink" title="unique_subquery"></a>unique_subquery</h4><p>在以下形式的某些<code>in</code>子查询中，这个join类型会替代<code>eq_ref</code>。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">value IN (<span class="keyword">SELECT</span> primary_key <span class="keyword">FROM</span> single_table <span class="keyword">WHERE</span> some_expr)</span><br></pre></td></tr></table></figure>

<p><code>unique_subquery</code>只是一个索引查找函数，它完全替代了子查询以提高效率。</p>
<h4 id="index-merge"><a href="#index-merge" class="headerlink" title="index_merge"></a>index_merge</h4><p>这个join类型表示使用了索引合并优化，联合了多个独立的索引来查找，这个时候，<code>key</code>列显示了一系列使用到的索引，<code>key_len</code>显示了使用到的索引的最长键长。</p>
<h4 id="fulltext"><a href="#fulltext" class="headerlink" title="fulltext"></a>fulltext</h4><p>使用全文索引执行join</p>
<h4 id="ref"><a href="#ref" class="headerlink" title="ref"></a>ref</h4><p>直接通过索引查找，返回所有<strong>匹配某个单个值</strong>的行，可能是多行</p>
<p>只有在使用<strong>非唯一索引或者唯一性索引的非唯一性前缀时</strong>才会发生</p>
<h4 id="ref-or-null"><a href="#ref-or-null" class="headerlink" title="ref_or_null"></a>ref_or_null</h4><p>ref的一个变体，对于包含null值行，MySQL会做一个额外的查询。这种join类型优化最常用于解析子查询。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ref_table</span><br><span class="line">  <span class="keyword">WHERE</span> key_column=expr <span class="keyword">OR</span> key_column <span class="keyword">IS</span> <span class="literal">NULL</span>;</span><br></pre></td></tr></table></figure>

<h4 id="eq-ref"><a href="#eq-ref" class="headerlink" title="eq_ref"></a>eq_ref</h4><p>也是通过索引查找，MySQL知道最多<strong>只返回一条</strong>符合条件的记录。一般发生在使用<strong>主键或者唯一性索引</strong>查找时发生</p>
<h4 id="const"><a href="#const" class="headerlink" title="const"></a>const</h4><p>当通过<strong>主键索引或者唯一索引</strong>查到只有一条记录时会显示const</p>
<h4 id="system"><a href="#system" class="headerlink" title="system"></a>system</h4><p>system是const的特例，表示所需查的表本身只有一条记录，这个表要是派生表，临时表，系统生成的表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> (<span class="keyword">select</span> <span class="number">1</span>) <span class="keyword">as</span> a</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> (<span class="keyword">select</span> * <span class="keyword">from</span> t <span class="keyword">LIMIT</span> <span class="number">1</span>) <span class="keyword">as</span> A</span><br></pre></td></tr></table></figure>

<h4 id="Null"><a href="#Null" class="headerlink" title="Null"></a>Null</h4><p>意味着MySQL能在优化阶段分解查询语句，根本不需要访问表或者遍历索引，例如直接求索引最大/最小值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">MIN</span>(actor_id) <span class="keyword">from</span> actor</span><br><span class="line"><span class="keyword">select</span> <span class="number">1</span></span><br></pre></td></tr></table></figure>

<h4 id="const-ref-eq-ref的区别"><a href="#const-ref-eq-ref的区别" class="headerlink" title="const,ref,eq_ref的区别"></a>const,ref,eq_ref的区别</h4><p>首先const和ref最主要的区别是使用的是否是唯一索引或者主键，</p>
<p>使用唯一索引来查找就是const</p>
<p>使用非唯一索引就是ref</p>
<p>最有问题的是const,<a href="https://stackoverflow.com/questions/4508055/what-does-eq-ref-and-ref-types-mean-in-mysql-explain" target="_blank" rel="noopener">ref和eq_ref的区别</a></p>
<p>按我的理解eq_ref主要是在有表关联的时候，两个表<strong>都是使用主键/唯一索引来关联</strong>的时候发生</p>
<p>因为此时A表的<strong>每一条记录</strong>对应的是B表的<strong>一条记录</strong></p>
<p>反之如果join表的时候使用非唯一索引来关联就是<strong>ref</strong></p>
<p>如果join表在非索引字段上，那就是<strong>ALL</strong></p>
<p>例子：假设现有几个表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">film (PrimaryKey(film_id))</span><br><span class="line">film_actor (PrimaryKey(actor_id,film_id))</span><br><span class="line">actor(PrimaryKey actor_id)</span><br><span class="line">actor_ext (PrimaryKey actor_id)</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--表actor,actor_ext通过主键关联，type:eq_ref</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> actor <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> actor_ext <span class="keyword">USING</span>(actor_id)</span><br><span class="line"></span><br><span class="line"><span class="comment">--表actor,film_actor通过主键非唯一前缀关联，type:ref</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> actor <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> film_actor <span class="keyword">USING</span>(actor_id)</span><br><span class="line"></span><br><span class="line"><span class="comment">--film_actor没有film_id索引，type:ALL</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> film <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> film_actor <span class="keyword">USING</span>(film_id)</span><br></pre></td></tr></table></figure>

<p>最终结论就是</p>
<table>
<thead>
<tr>
<th></th>
<th>单表</th>
<th>多表关联</th>
</tr>
</thead>
<tbody><tr>
<td>主键/唯一索引</td>
<td>const</td>
<td>eq_ref</td>
</tr>
<tr>
<td>非唯一索引/主键非唯一前缀</td>
<td>ref</td>
<td>ref</td>
</tr>
</tbody></table>
<h3 id="possible-keys列"><a href="#possible-keys列" class="headerlink" title="possible_keys列"></a>possible_keys列</h3><p>显示该行查询可以使用哪些索引，这些列实在优化过程早期创建的，游戏恶劣罗列出来的索引可能其实没用到。</p>
<h3 id="key列"><a href="#key列" class="headerlink" title="key列"></a>key列</h3><p>显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL</p>
<p>key_len列</p>
<p>使用到索引字段的长度单位：字节</p>
<p>key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。</p>
<p>表film_actor 主键actor_id,film_id都是smallint 各占2字节</p>
<p>varchar类型的话key_len不固定</p>
<p>char类型如果是utf8 一个字符3字节</p>
<p>如果是LIKE前缀匹配也是会显示整个字段的完全宽度被使用</p>
<p>由于索引存储格式，一个允许空值的列，索引长度要比非空列要长。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> film_actor <span class="keyword">where</span> actor_id=<span class="number">1</span>  <span class="comment">--key_len:2</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> film_actor <span class="keyword">where</span> actor_id=<span class="number">1</span> <span class="keyword">and</span> film_id=<span class="number">277</span>  <span class="comment">--key_len:4</span></span><br></pre></td></tr></table></figure>

<h3 id="ref列"><a href="#ref列" class="headerlink" title="ref列"></a>ref列</h3><p>本列显示了哪一列或那个常量被用来和索引进行比较从而从表中筛选行。</p>
<p>如果值为func,表明使用了某些函数的结果。这个函数实际上可能是一个运算符，比如算术运算符 。</p>
<h3 id="rows列"><a href="#rows列" class="headerlink" title="rows列"></a>rows列</h3><p>表示MySQL认为执行查询语句时必须查找的行数（不是最终结果的行数）。在InnoDB引擎中，这个数值是个预估值，可能会不精确。</p>
<h3 id="filtered列"><a href="#filtered列" class="headerlink" title="filtered列"></a>filtered列</h3><p>表是符合where或者联接条件的记录数的百分比，如果与前面的rows列的数据相乘就是最终参与到下轮查询的记录数</p>
<p>如actor表有200条记录，filtered是10，rows是200,最终查出200*10%=2条（无索引），</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> actor <span class="keyword">where</span> actor.first_name=<span class="string">'KIRSTEN'</span></span><br></pre></td></tr></table></figure>

<table>
<thead>
<tr>
<th>type</th>
<th>rows</th>
<th>filtered</th>
</tr>
</thead>
<tbody><tr>
<td>ALL</td>
<td>200</td>
<td>10</td>
</tr>
</tbody></table>
<h3 id="Extra列"><a href="#Extra列" class="headerlink" title="Extra列"></a>Extra列</h3><p><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information" target="_blank" rel="noopener">explain-extra-information</a></p>
<p>不适合显示在以上列的其他额外信息</p>
<table>
<thead>
<tr>
<th><strong>“Distinct”</strong></th>
<th>MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。</th>
</tr>
</thead>
<tbody><tr>
<td><strong>*”Not exists”*</strong></td>
<td>MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。</td>
</tr>
<tr>
<td><strong>*”Using index”*</strong></td>
<td>索引覆盖</td>
</tr>
<tr>
<td><strong>*”Using where Using index”*</strong></td>
<td>查询的列被<strong>索引覆盖</strong>，并且where筛选条件是索引列之一但是不是索引的前导列</td>
</tr>
<tr>
<td><strong>*”Using index condition”*</strong></td>
<td>查询的字段有未被索引的字段需要回表查询</td>
</tr>
<tr>
<td><strong>*”Using where”*</strong></td>
<td>存储引擎检索数据时无法直接（通过索引）筛选数据，要返回给上层再过滤</td>
</tr>
<tr>
<td><strong>*”Using filesort”*</strong></td>
<td>将对结果进行外部排序</td>
</tr>
<tr>
<td><strong>“Using join buffer”</strong></td>
<td>使用join语句</td>
</tr>
<tr>
<td><strong>“Using temporary”</strong></td>
<td>创建了临时表来存储数据</td>
</tr>
<tr>
<td><strong>*”Using index for group-by”*</strong></td>
<td>使用松散索引</td>
</tr>
<tr>
<td><strong>*”Use intersect(a,b)”*</strong></td>
<td>根据两个索引分别求结果然后求交集得出</td>
</tr>
<tr>
<td>##### Using where Using index、Using index condition区别</td>
<td></td>
</tr>
</tbody></table>
<p>Using index condition：select列表有未被索引的字段，但是where字段有索引，意味着可以通过索引来筛选部分字段(ICP索引下推)，但最终<strong>还是要回表</strong></p>
<p>如下例子，length有索引，但select字段不是索引覆盖</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> film <span class="keyword">where</span> <span class="keyword">length</span>  <span class="keyword">BETWEEN</span> <span class="number">40</span> <span class="keyword">and</span> <span class="number">50</span></span><br></pre></td></tr></table></figure>

<p>Using where;Using index: 查询的select字段被索引覆盖了，但是where条件不是索引的前导字段，只是一部分,然后索引下推ICP</p>
<p>如下例子，film_actor (PrimaryKey(actor_id,film_id))</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> actor_id,film_id <span class="keyword">from</span> film_actor <span class="keyword">where</span> film_id=<span class="number">277</span></span><br></pre></td></tr></table></figure>

<p>select字段索引覆盖，但是film_id正常来说是用不到索引的，但由于索引下推？可以使用这个来进行行筛选，最终不需要回表就能获得数据</p>
<p>由于一个需要回表查询一个不需要回表，因此</p>
<p><strong>“Using index”&gt;”Using where;Using index” &gt; “Using index condition”</strong></p>
<h2 id="参考资料"><a href="#参考资料" class="headerlink" title="参考资料"></a>参考资料</h2><p>《高性能MySQL第3版》</p>
<p><a href="https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_system" target="_blank" rel="noopener">MySQL官方文档</a></p>
<p><a href="https://www.linuxidc.com/Linux/2017-08/146297.htm" target="_blank" rel="noopener">using index 和 using where using index 的区别</a></p>
<p><a href="https://www.jianshu.com/p/19c3072b86dd" target="_blank" rel="noopener">MySQL优化—EXPLAIN（mysq版本8.0）</a></p>
<p><a href="https://www.cnblogs.com/cheng21553516/p/11403378.html" target="_blank" rel="noopener">Mysql优化之Explain查询计划查看</a></p>

        
<script src="/blog/js/qrious.min.js"></script>


        <hr>
        <div>
            
            <p>
                <span>专题: </span>
                
                <a href="/blog/series/#MySQL">
                    <code key="MySQL" class="post-label">MySQL</code>
                </a>
                
            </p>
            
            <p>
                <span>本文发表于 2020-03-03，最后修改于 2020-03-31。</span>
            </p>
            <!-- 文章末尾的提示 start -->
            
            
            
                
            <!-- 文章末尾的提示 end -->
        </div>
        <hr />
        <p name="pagination" style="font-size: 1.2em">
    
    <a class="float-left" href="/blog/post/mysql-index-base.html">上一篇 « 《高性能MySQL》笔记-索引基础</a>
    
    
    <a class="float-right" href="/blog/post/go-learn-lib-sorts.html">下一篇 » Go源码学习 sort包</a>
    
</p>
    </article>
    <!-- 赞赏 -->
    <!--打赏-->

    <!-- 推荐阅读三篇文章 -->
    <div class="col-xs-12">
        <h3>推荐阅读</h3>
        
        <div class="post-preview">
    <div class="post-img" style="background-image: url('/blog/img/database/database.webp')"></div>
    <div class="post-info">
        <div class="post-info-center">
            <div class="hidden-xs">
                
                
                <span>/</span>
                
                <a class="text-gray" href="/blog/tags/#MySQL"
                    title="MySQL">MySQL</a>
                <span>/</span>
                
                <a class="text-gray" href="/blog/tags/#MySQL优化"
                    title="MySQL优化">MySQL优化</a>
                <span>/</span>
                
                
            </div>
            <a href="/blog/post/mysql-optimize-explain.html" class="title">
                EXPLAIN-执行计划查询命令
            </a>
            <p class="text-gray">
                <small>
                    <span>发表于2020-03-03，</span>
                    <span class="hidden-xs">全文11409字，</span>
                    <span>阅读约39分钟</span>
                </small>
            </p>
        </div>
    </div>
</div>
        
        <div class="post-preview">
    <div class="post-img" style="background-image: url('/blog/img/tcp/tcp.webp')"></div>
    <div class="post-info">
        <div class="post-info-center">
            <div class="hidden-xs">
                
                
                <span>/</span>
                
                <a class="text-gray" href="/blog/tags/#基础"
                    title="基础">基础</a>
                <span>/</span>
                
                <a class="text-gray" href="/blog/tags/#tcp/http"
                    title="tcp/http">tcp/http</a>
                <span>/</span>
                
                
            </div>
            <a href="/blog/post/tcp-three-way-handshake.html" class="title">
                简述TCP“3次握手，4次挥手”
            </a>
            <p class="text-gray">
                <small>
                    <span>发表于2020-02-21，</span>
                    <span class="hidden-xs">全文9978字，</span>
                    <span>阅读约34分钟</span>
                </small>
            </p>
        </div>
    </div>
</div>
        
        <div class="post-preview">
    <div class="post-img" style="background-image: url('/blog/img/related_links/relatelink.webp')"></div>
    <div class="post-info">
        <div class="post-info-center">
            <div class="hidden-xs">
                
                
                <span>/</span>
                
                <a class="text-gray" href="/blog/tags/#友链"
                    title="友链">友链</a>
                <span>/</span>
                
                
            </div>
            <a href="/blog/post/link.html" class="title">
                友链
            </a>
            <p class="text-gray">
                <small>
                    <span>发表于2011-02-03，</span>
                    <span class="hidden-xs">全文241字，</span>
                    <span>阅读约1分钟</span>
                </small>
            </p>
        </div>
    </div>
</div>
        
    </div>
    <div class="col-xs-12">
        <!-- 标签列表 -->
        <!-- Featured Tags -->
<style>
    #featured-tag .post-tag-item {
        font-size: 12px;
        line-height: 30px;
        display: inline-block;
        height: 30px;
        margin: 5px 0px;
        padding: 0 7px;
        color: #333;
        border-radius: 15px;
        background: #f6f6f6;
    }

    #featured-tag .post-tag-item:hover {
        color: #337ab7;
    }
</style>
<div id="featured-tag">
    
    <a class="post-tag-item" href="/blog/tags/#关于我" title="关于我"
        rel="1">#关于我 (1) </a>
    
    <a class="post-tag-item" href="/blog/tags/#友链" title="友链"
        rel="1">#友链 (1) </a>
    
    <a class="post-tag-item" href="/blog/tags/#Golang汇编" title="Golang汇编"
        rel="4">#Golang汇编 (4) </a>
    
    <a class="post-tag-item" href="/blog/tags/#Golang底层" title="Golang底层"
        rel="5">#Golang底层 (5) </a>
    
    <a class="post-tag-item" href="/blog/tags/#Golang" title="Golang"
        rel="8">#Golang (8) </a>
    
    <a class="post-tag-item" href="/blog/tags/#Golang基础" title="Golang基础"
        rel="1">#Golang基础 (1) </a>
    
    <a class="post-tag-item" href="/blog/tags/#Golang源码" title="Golang源码"
        rel="2">#Golang源码 (2) </a>
    
    <a class="post-tag-item" href="/blog/tags/#基础" title="基础"
        rel="2">#基础 (2) </a>
    
    <a class="post-tag-item" href="/blog/tags/#tcp/http" title="tcp/http"
        rel="2">#tcp/http (2) </a>
    
    <a class="post-tag-item" href="/blog/tags/#MySQL" title="MySQL"
        rel="3">#MySQL (3) </a>
    
    <a class="post-tag-item" href="/blog/tags/#MySQL优化" title="MySQL优化"
        rel="1">#MySQL优化 (1) </a>
    
</div>
    </div>
    <!-- 评论 -->
    <div class="col-xs-12">
        
    </div>
</div>
<aside class="float-left gkt-sidebar hidden-xs hidden-sm">
    <div style="clear: both"></div>
    <div class="gkt-sidebar-wrapper">
        <section class="box-shadow"><style>
    .gkt-summary {
        border: 1px solid #DDDDDD;
        border-radius: 3px;
        padding: 5px;
        width: 100%;
    }


    .gkt-summary nav {
        overflow: hidden;
    }

    .gkt-summary nav a {
        display: inline-block;
        text-align: center;
        color: #333;
        font-size: 12px;
    }

    .gkt-summary nav span {
        display: block;
    }

    .gkt-summary nav .middle {
        border-left: 1px solid #eaecef;
        border-right: 1px solid #eaecef;
    }

    .gkt-summary .number {
        font-weight: bold;
    }

    .gkt-summary .link-list {
        margin-top: 5px;
        margin-bottom: -5px;
        padding-top: 7px;
        border-top: 1px dashed #999;
        display: flex;
    }

    .gkt-summary .link-list a {
        flex: 1;
    }

    .gkt-summary .link-list img {
        width: 25px;
        height: 25px;
    }
</style>

<div class="gkt-summary">
    <nav>
        <a href="/blog/" class="col-xs-4">
            <number class="number">15</number><span>文章</span>
        </a>
        <a href="/blog/series" class="col-xs-4 middle">
            <number class="number">5</number><span>专题</span>
        </a>
        <a href="/blog/tags" class="col-xs-4">
            <number class="number">11</number><span>标签</span>
        </a>
    </nav>

    
    <div class="link-list">
        
        <a href="https://marvelthanos.com" target="_blank" rel="noopener"><img src="/blog/img/related_links/thanos.webp" /></a>
        
    </div>
    
</div></section>
        
        
        <section class="gkt-sidebar-content box-shadow">
            <strong>MySQL</strong>
            <ul>
                
                <li>
                    <a href="/blog/post/mysql-index-use.html"
                        class="">《高性能MySQL》笔记-索引优化</a>
                    
                </li>
                
                <li>
                    <a href="/blog/post/mysql-index-base.html"
                        class="">《高性能MySQL》笔记-索引基础</a>
                    
                </li>
                
                <li>
                    <a href="/blog/post/mysql-optimize-explain.html"
                        class="gkt-sidebar-active">EXPLAIN-执行计划查询命令</a>
                    
                    <!-- Table of Contents -->
<div id="sidebar-toc">
  <!-- TOC  -->
  
  <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#Explain-输出的列"><span class="toc-nav-text">Explain 输出的列</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#介绍"><span class="toc-nav-text">介绍</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#局限"><span class="toc-nav-text">局限</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#调用"><span class="toc-nav-text">调用</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#输出列介绍"><span class="toc-nav-text">输出列介绍</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#参考资料"><span class="toc-nav-text">参考资料</span></a></li></ol>
  
</div>

<script>
  (function () {
    var h2 = document.querySelectorAll('article h2');
    var h3 = document.querySelectorAll('article h3');
    var linkList = document.querySelectorAll('#sidebar-toc a');

    function findLinkElement(name) {
      for (var i = 0; i < linkList.length; i++) {
        var items = linkList[i].href.split('#');
        if (items && items[items.length - 1] === encodeURIComponent(name)) {
          return i;
        }
      }
      return -1;
    }

    function activeLink(titleList) {
      var scrollTop = document.documentElement.scrollTop || document.body.scrollTop;
      for (var i = titleList.length - 1; i >= 0; i--) {
        if (scrollTop - titleList[i].offsetTop > 0) {
          var index = findLinkElement(titleList[i].id);
          index != -1 && linkList[index].classList.add('gkt-sidebar-active');
          break;
        }
      }
    }

    window.addEventListener("scroll", function (e) {
      [].slice.call(linkList).forEach(function (link) {
        link.classList.remove('gkt-sidebar-active');
      })
      activeLink(h2);
    })
  })();
</script>
                    
                </li>
                
            </ul>
        </section>
        
        
        <section class="box-shadow">
            <strong>最近的文章</strong>
            <ul>
                
                <li>
                    <a href="/blog/post/go-learn-lib-sorts.html">Go源码学习 sort包</a>
                </li>
                
                <li>
                    <a href="/blog/post/mysql-optimize-explain.html">EXPLAIN-执行计划查询命令</a>
                </li>
                
                <li>
                    <a href="/blog/post/mysql-index-base.html">《高性能MySQL》笔记-索引基础</a>
                </li>
                
            </ul>
        </section>
        <section class="box-shadow text-center">
            
        </section>
    </div>
</aside>

<script>
    (function () {
        var ele = getDom('.gkt-sidebar-content')
        var wrapper = getDom('.gkt-sidebar-wrapper')
        var last = 0
        window.addEventListener("scroll", function (e) {
            var scrollTop = document.documentElement.scrollTop || document.body.scrollTop;
            var isDown = scrollTop > last;
            var pos = getPosition(ele).y - scrollTop;
            var downLimit = 50;
            var upLimit = -100;
            // uarrow.style.marginTop = scrollTop + 'px';
            isDown && pos <= downLimit && wrapper.classList.add("gkt-sidebar-fixed");
            !isDown && pos > upLimit && wrapper.classList.remove("gkt-sidebar-fixed");
            last = scrollTop
        })
    })();
    (function () {
        function resizeUArrow() {
            var s = getDom('.u-arrow-wrapper').style
            var pc = getDom('.post-container')
            s.left = getPosition(pc).x + 'px';
            s.width = pc.clientWidth + 'px';
        }
        resizeUArrow()
        window.addEventListener('resize', resizeUArrow);
    })();
</script>

    
</main>
    </div>
    <style>
    img#go-top {
        position: fixed;
        bottom: 100px;
        width: 50px;
        cursor: pointer;
        z-index: 9999;
    }
</style>
<img id="go-top" src="/blog/icon/top.png" class="hidden-xs" style="display: none" />
<script>
    (function () {
        var goTop = document.getElementById('go-top');
        var mainContainer = document.querySelector('.main-container');
        
        goTop.addEventListener('click', function () {
            window.scroll(0, 0);
        }, false);
        window.addEventListener('scroll', function () {
            var right = document.body.offsetWidth - mainContainer.getBoundingClientRect().right;
            var scrollTop = document.documentElement.scrollTop || document.body.scrollTop;
            goTop.style.right = right + 10 + 'px'
            scrollTop > 700 && (goTop.style.display = "block");
            scrollTop <= 700 && (goTop.style.display = "none");
        });
    })();
</script>
    <style>
    #geektutu-click-img-container {
        position: fixed;
        left: 0;
        top: 0;
        text-align: center;
        width: 100%;
        display: none;
        z-index: 9999;
    }

    #geektutu-click-img-container img {
        object-fit: contain;
        background: #eaecef;
        padding: 15px;
        border-radius: 10px;
        height: auto;
        width: auto;
        vertical-align: middle;
    }
</style>


<div id="geektutu-click-img-container">
    <img src="" alt="Big Image">
</div>

<script>
    (function () {
        var container = document.querySelector('#geektutu-click-img-container')
        var targetImg = container.querySelector('img')
        var imgs = document.querySelectorAll('article img');
        targetImg.addEventListener('click', function (e) {
            container.style.display = 'none';
            e && e.stopPropagation && e.stopPropagation();
        }, false);

        for (var i = 0; i < imgs.length; ++i) {
            var img = imgs[i];
            img.addEventListener('click', (function (src, rate) {
                return function (e) {
                    e && e.stopPropagation && e.stopPropagation();
                    if (window.innerWidth < 980) {
                        return
                    }
                    targetImg.style.height = targetImg.style.width = 'auto';
                    if (window.innerWidth / window.innerHeight > rate) {
                        targetImg.style.height = (window.innerHeight - 20) + 'px';
                    } else {
                        targetImg.style.width = (window.innerWidth - 20) + 'px';
                    }
                    container.style.height = window.innerHeight + 'px'
                    container.style.lineHeight = window.innerHeight + 'px'
                    container.style.display = 'block';
                    targetImg.src = src;
                };
            }(img.src, img.width / img.height)), false)
        }
    })();
</script>
    <!-- Footer -->
    <!-- Footer -->
<style>
    footer {
        width: 100%;
        line-height: 1.5;
        padding: 20px;
    }

    footer a {
        color: #333;
        text-decoration: none;
    }

    .footer-hexo img {
        height: 20px;
        margin-bottom: -5px;
    }

    .footer-hexo a {
        color: #337ab7;
    }
</style>
<footer class="text-center col-xs-12">
    <p>
        <small>© 2020 - Evrn - </small>
        <small>
            <a target="_blank" rel="nofollow noopener" href="http://www.miitbeian.gov.cn/"></a>
        </small>
    </p>
    <p class="footer-hexo">
        <!-- 但若直接使用或修改主题，请务必保留这段声明 -->
        <small>Powered by <a target="_blank" href="https://hexo.io">Hexo</a> | Theme
            <a target="_blank" href="https://geektutu.com">Geektutu</a>
            <a href="https://github.com/geektutu/hexo-theme-geektutu" target="_blank" rel="noopener">
                <img src=""
                    alt="Github Star">
            </a>
        </small>
    </p>
    
    <p>
        <small>
            <span id="busuanzi_container_site_pv">👁<span id="busuanzi_value_site_pv"></span></span> &nbsp;
            <span id="busuanzi_container_page_pv">📚<span id="busuanzi_value_page_pv"></span></span>
        </small>
    </p>
    
    

</footer>


<script>
    window.addEventListener('load', function () {
        globalAddScript('//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js');
    })
</script>

<!-- Baidu Tongji -->





    <script>
        // 非本站网址，新开tab页签
        (function () {
            var stopBubble = function (e) {
                e && e.stopPropagation && e.stopPropagation();
            }
            var links = document.querySelectorAll('a');
            [].slice.call(links).forEach(function (item) {
                if (item.href && item.href.indexOf(window.location.host) === -1) {
                    item.target = '_blank'
                }
                // 阻止冒泡，不触发彩蛋。
                item.addEventListener('click', stopBubble, false);
            });
            var article = document.querySelector('article');
            article && article.addEventListener('click', stopBubble, false)
        })();
    </script>
    
</body>

</html>